from sqlalchemy import create_engine, MetaData, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.pool import QueuePool
from typing import Generator
import logging
import pymysql
from app.config.settings import settings

# 安装pymysql作为MySQLdb的替代
pymysql.install_as_MySQLdb()

# 配置日志
logger = logging.getLogger(__name__)

# 创建数据库引擎——负责管理数据库连接，处理与数据库的所有低级通信
engine = create_engine(
    settings.database_url,
    echo=settings.database_echo,
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=3600,
    connect_args={
        "charset": "utf8mb4",
        "autocommit": False,
    }
)

# 创建会话工厂——用于生成与数据库的会话对象，会话对象是应用程序与数据库交互的主要接口
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

# 创建基础模型类——提供了 ORM 映射的核心功能，即python类与数据库表之间的映射关系
Base = declarative_base()

# 元数据——存储表结构信息、提供数据库交互入口、支持反射功能
metadata = MetaData()

def get_db() -> Generator[Session, None, None]:
    """获取数据库会话"""
    db = SessionLocal()
    try:
        yield db
    except Exception as e:
        logger.error(f"数据库会话错误: {e}")
        db.rollback()
        raise
    finally:
        db.close()

def create_tables():
    """创建所有数据表"""
    try:
        Base.metadata.create_all(bind=engine)
        logger.info("数据表创建成功")
    except Exception as e:
        logger.error(f"数据表创建失败: {e}")
        raise

def drop_tables():
    """删除所有数据表"""
    try:
        Base.metadata.drop_all(bind=engine)
        logger.info("数据表删除成功")
    except Exception as e:
        logger.error(f"数据表删除失败: {e}")
        raise

def check_connection():
    """检查数据库连接，返回是否连接正常"""
    try:
        with engine.connect() as connection:
            connection.execute(text("SELECT 1"))
        logger.info("数据库连接正常")
        return True
    except Exception as e:
        logger.error(f"数据库连接失败: {e}")
        return False

def init_db():
    """初始化数据库"""
    logger.info("开始初始化数据库...")
    # 检查连接
    if not check_connection():
        raise Exception("数据库连接失败，无法初始化")
    # 创建表
    create_tables()
    logger.info("数据库初始化完成")

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.engine = engine
        self.SessionLocal = SessionLocal
    
    def get_session(self) -> Session:
        """获取数据库会话"""
        return self.SessionLocal()
    
    def execute_raw_sql(self, sql: str, params: dict = None):
        """执行原生SQL，params为参数字典，支持带参数的SQL语句。返回查询结果"""
        with self.engine.connect() as connection:
            if params:
                result = connection.execute(text(sql), params)
            else:
                result = connection.execute(text(sql))
            return result.fetchall()
    
    def backup_database(self, backup_path: str):
        """备份数据库"""
        # 这里可以实现数据库备份逻辑
        logger.info(f"数据库备份到: {backup_path}")
        pass
    
    def restore_database(self, backup_path: str):
        """恢复数据库"""
        # 这里可以实现数据库恢复逻辑
        logger.info(f"从备份恢复数据库: {backup_path}")
        pass

# 创建数据库管理器实例
db_manager = DatabaseManager()